#! /bin/bash

hive  -S -e  "
-- 需求1：每天/月/年各个校区的报名人数
drop table if exists bxg_rpt.school_signup;
create table bxg_rpt.school_signup(
    year            string comment '年',
    year_month      string comment '月',
    year_month_day  string comment '日',
    school_name     string comment '校区名',
    signup_count    bigint comment '报名人数'
)
comment '各个校区的报名人数统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求1.1：每天各个校区的报名人数
drop table if exists bxg_rpt.day_school_signup;
create table bxg_rpt.day_school_signup(
    year_month_day  string comment '日',
    school_name     string comment '校区名',
    signup_count    bigint comment '报名人数'
)
comment '各个校区的报名人数日统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求1.2：每月各个校区的报名人数
drop table if exists bxg_rpt.month_school_signup;
create table bxg_rpt.month_school_signup(
    year_month      string comment '月',
    school_name     string comment '校区名',
    signup_count    bigint comment '报名人数'
)
comment '各个校区的报名人数月统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求1.3：每年各个校区的报名人数
drop table if exists bxg_rpt.year_school_signup;
create table bxg_rpt.year_school_signup(
    year            string comment '年',
    school_name     string comment '校区名',
    signup_count    bigint comment '报名人数'
)
comment '各个校区的报名人数年统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求2：线上线下各个校区的报名人数
drop table if exists bxg_rpt.origintype_school_signup;
create table bxg_rpt.origintype_school_signup(
    year            string comment '年',
    year_month      string comment '月',
    year_month_day  string comment '日',
    school_name     string comment '校区名',
    origin_type     string comment '线上线下',
    signup_count    bigint comment '报名人数'
)
comment '线上线下各个校区的报名人数日统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求2.1：每天线上线下各个校区的报名人数
drop table if exists bxg_rpt.day_origintype_school_signup;
create table bxg_rpt.day_origintype_school_signup(
    year_month_day  string comment '日',
    school_name     string comment '校区名',
    origin_type     string comment '线上线下',
    signup_count    bigint comment '报名人数'
)
comment '线上线下各个校区的报名人数日统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求2.2：每月线上线下各个校区的报名人数
drop table if exists bxg_rpt.month_origintype_school_signup;
create table bxg_rpt.month_origintype_school_signup(
    year_month      string comment '月',
    school_name     string comment '校区名',
    origin_type     string comment '线上线下',
    signup_count    bigint comment '报名人数'
)
comment '线上线下各个校区的报名人数月统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求2.3：每年线上线下各个校区的报名人数
drop table if exists bxg_rpt.year_origintype_school_signup;
create table bxg_rpt.year_origintype_school_signup(
    year            string comment '年',
    school_name     string comment '校区名',
    origin_type     string comment '线上线下',
    signup_count    bigint comment '报名人数'
)
comment '线上线下各个校区的报名人数年统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求6.1：线上线下各个咨询中心的报名人数
drop table if exists  bxg_rpt.origintype_department_signup;
create table bxg_rpt.origintype_department_signup(
    year            string comment '年',
    year_month      string comment '月',
    year_month_day  string comment '日',
    department_name string comment '咨询中心名',
    origin_type     string comment '线上线下',
    signup_count    bigint comment '报名人数'
)
comment '线上线下各个咨询中心的报名人数日统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求6.1：每天线上线下各个咨询中心的报名人数
drop table if exists  bxg_rpt.day_origintype_department_signup;
create table bxg_rpt.day_origintype_department_signup(
    year_month_day  string comment '日',
    department_name string comment '咨询中心名',
    origin_type     string comment '线上线下',
    signup_count    bigint comment '报名人数'
)
comment '线上线下各个咨询中心的报名人数日统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求6.2：每月线上线下各个咨询中心的报名人数
drop table if exists  bxg_rpt.month_origintype_department_signup;
create table bxg_rpt.month_origintype_department_signup(
    year_month  string comment '月',
    department_name string comment '咨询中心名',
    origin_type     string comment '线上线下',
    signup_count    bigint comment '报名人数'
)
comment '线上线下各个咨询中心的报名人数月统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求6.3：每年线上线下各个咨询中心的报名人数
drop table if exists  bxg_rpt.year_origintype_department_signup;
create table bxg_rpt.year_origintype_department_signup(
    year            string comment '年',
    department_name string comment '咨询中心名',
    origin_type     string comment '线上线下',
    signup_count    bigint comment '报名人数'
)
comment '线上线下各个咨询中心的报名人数年统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求7：线上线下的意向转报名率 = 报名人数 / 意向人数
drop table if exists bxg_rpt.origintype_signup_relationship_ratio;
create table bxg_rpt.origintype_signup_relationship_ratio(
    year            string comment '年',
    year_month      string comment '月',
    year_month_day  string comment '日',
    origin_type     string comment '线上线下',
    signup_relationship_ratio    decimal(5,2) comment '意向转报名率'
)
comment '线上线下的意向转报名率日统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');
-- 需求7.1：每天线上线下的意向转报名率 = 报名人数 / 意向人数
drop table if exists bxg_rpt.day_origintype_signup_relationship_ratio;
create table bxg_rpt.day_origintype_signup_relationship_ratio(
    year_month_day  string comment '日',
    origin_type     string comment '线上线下',
    signup_relationship_ratio    decimal(5,2) comment '意向转报名率'
)
comment '线上线下的意向转报名率日统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');


-- 需求7.2：每月线上线下的意向转报名率 = 报名人数 / 意向人数
drop table if exists bxg_rpt.month_origintype_signup_relationship_ratio;
create table bxg_rpt.month_origintype_signup_relationship_ratio(
    year_month      string comment '月',
    origin_type     string comment '线上线下',
    signup_relationship_ratio   decimal(5,2) comment '意向转报名率'
)
comment '线上线下的意向转报名率月统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求7.3：每年线上线下的意向转报名率 = 报名人数 / 意向人数
drop table if exists bxg_rpt.year_origintype_signup_relationship_ratio;
create table bxg_rpt.year_origintype_signup_relationship_ratio(
    year            string comment '月',
    origin_type     string comment '线上线下',
    signup_relationship_ratio    decimal(5,2) comment '意向转报名率'
)
comment '线上线下的意向转报名率年统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求8：线上的有效线索报名转换率 = 报名人数 / 有效线索人数
drop table if exists bxg_rpt.origintype_signup_clue_ratio;
create table bxg_rpt.origintype_signup_clue_ratio (
    year            string comment '年',
    year_month      string comment '月',
    year_month_day  string comment '日',
    origin_type     string comment '线上线下',
    signup_clue_ratio    decimal(12,2) comment '有效线索报名转换率'
)
comment '线上的有效线索报名转换率日统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求8.1：每天线上的有效线索报名转换率 = 报名人数 / 有效线索人数
drop table if exists bxg_rpt.day_origintype_signup_clue_ratio;
create table bxg_rpt.day_origintype_signup_clue_ratio (
    year_month_day  string comment '日',
    origin_type     string comment '线上线下',
    signup_clue_ratio    decimal(12,2) comment '有效线索报名转换率'
)
comment '线上的有效线索报名转换率日统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求8.2：每月线上的有效线索报名转换率 = 报名人数 / 有效线索人数
drop table if exists bxg_rpt.month_origintype_signup_clue_ratio;
create table bxg_rpt.month_origintype_signup_clue_ratio (
    year_month      string comment '月',
    origin_type     string comment '线上线下',
    signup_clue_ratio    decimal(12,2) comment '有效线索报名转换率'
)
comment '线上的有效线索报名转换率月统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');

-- 需求8.3：每年线上的有效线索报名转换率 = 报名人数 / 有效线索人数
drop table if exists bxg_rpt.year_origintype_signup_clue_ratio;
create table bxg_rpt.year_origintype_signup_clue_ratio (
    year            string comment '年',
    origin_type     string comment '线上线下',
    signup_clue_ratio    decimal(12,2) comment '有效线索报名转换率'
)
comment '线上的有效线索报名转换率年统计表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='SNAPPY');"